// Copyright (c) Microsoft Corporation.  All rights reserved.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Query;
using System.Data.DLinq;
using System.IO;
using System.Transactions;
//using System.Expressions;
//using System.Data.DLinq.Expressions;
using System.Reflection;
using SampleSupport;
using nwind;

namespace SampleQueries {
    [Title("101+ DLinq Query Samples")]
    [Prefix("DLinq")]
    public class DLinqSamples : SampleHarness 
    { 
        private readonly static string connString = "Data Source=XE;User ID=NORTHWIND;Password=pa55w#rd;";

        private static readonly nwind.Northwind db = new Northwind(connString);

        [CategoryAttribute("WHERE")]
        [Title("Where - 1")]
        [Description("This sample uses WHERE to filter for Customers in London.")]
        public void DLinq1() {

            var q =
                from c in db.Customers
                where c.City == "London"
                select c;
            ObjectDumper.Write(q);
        }

        [CategoryAttribute("WHERE")]
        [Title("Where - 2")]
        [Description("This sample uses WHERE to filter for Employees hired " +
                     "during or after 1994.")]
        public void DLinq2() {
            var q =
                from e in db.Employees
                where e.HireDate >= new DateTime(1994, 1, 1)
                select e;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("WHERE")]
        [Title("Where - 3")]
        [Description("This sample uses WHERE to filter for Products that have stock below their " +
                     "reorder level and are not discontinued.")]
        public void DLinq3() {
            var q =
                from p in db.Products
                where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued
                select p;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("WHERE")]
        [Title("Where - 4")]
        [Description("This sample uses WHERE to filter out Products that are either " +
                     "UnitPrice is greater than 10 or is discontinued.")]
        public void DLinq4() {
            var q =
                from p in db.Products
                where p.UnitPrice > 10m || p.Discontinued
                select p;

            ObjectDumper.Write(q, 0);
        }

        [CategoryAttribute("WHERE")]
        [Title("Where - 5")]
        [Description("This sample calls WHERE twice to filter out Products that UnitPrice is greater than 10" +
                     " and is discontinued.")]
        public void DLinq5() {
            var q =
                db.Products.Where(p=>p.UnitPrice > 10m).Where(p=>p.Discontinued);

            ObjectDumper.Write(q, 0);
        }

        [CategoryAttribute("WHERE")]
        [Title("First - Simple")]
        [Description("This sample uses First to select the first Shipper in the table.")]
        public void DLinq6()
        {
            Shipper shipper = db.Shippers.First();
            ObjectDumper.Write(shipper, 0);
        }

        [CategoryAttribute("WHERE")]
        [Title("First - Element")]
        [Description("This sample uses First to select the single Customer with CustomerID 'BONAP'.")]
        public void DLinq7() {
            Customer cust = db.Customers.First(c => c.CustomerID == "BONAP");
            ObjectDumper.Write(cust, 0);
        }

        [CategoryAttribute("WHERE")]
        [Title("First - Condition")]
        [Description("This sample uses First to select an Order with freight greater than 10.00.")]
        public void DLinq8() {
            Order ord = db.Orders.First(o => o.Freight > 10.00M);
            ObjectDumper.Write(ord, 0);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Simple")]
        [Description("This sample uses SELECT to return a sequence of just the " +
                     "Customers' contact names.")]
        public void DLinq9() {
            var q =
                from c in db.Customers
                select c.ContactName;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Anonymous Type 1")]
        [Description("This sample uses SELECT and anonymous types to return " +
                     "a sequence of just the Customers' contact names and phone numbers.")]
        public void DLinq10() {
            var q =
                from c in db.Customers
                select new {c.ContactName, c.Phone};

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Anonymous Type 2")]
        [Description("This sample uses SELECT and anonymous types to return " +
                     "a sequence of just the Employees' names and phone numbers, " +
                     "with the FirstName and LastName fields combined into a single field, 'Name', " +
                     "and the HomePhone field renamed to Phone in the resulting sequence.")]
        public void DLinq11() {
            var q =
                from e in db.Employees
                select new {Name = e.FirstName + " " + e.LastName, Phone = e.HomePhone};

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Anonymous Type 3")]
        [Description("This sample uses SELECT and anonymous types to return " +
                     "a sequence of all Products' IDs and a calculated value " +
                     "called HalfPrice which is set to the Product's UnitPrice " +
                     "divided by 2.")]
        public void DLinq12() {
            var q =
                from p in db.Products
                select new {p.ProductID, HalfPrice = p.UnitPrice / 2};
            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Conditional ")]
        [Description("This sample uses SELECT and a conditional statment to return a sequence of product " +
                     " name and product availability.")]
        public void DLinq13() {
            var q =
                from p in db.Products
                select new {p.ProductName, Availability = p.UnitsInStock - p.UnitsOnOrder < 0 ? "Out Of Stock": "In Stock"};

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Named Type")]
        [Description("This sample uses SELECT and a known type to return a sequence of employees' names.")]
        public void DLinq14() {
            var q =
                from e in db.Employees                
                select new {FirstName = e.FirstName, LastName = e.LastName};

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Filtered")]
        [Description("This sample uses SELECT and WHERE to return a sequence of " +
                     "just the London Customers' contact names.")]
        public void DLinq15() {
            var q =
                from c in db.Customers
                where c.City == "London"
                select c.ContactName;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Shaped")]
        [Description("This sample uses SELECT and anonymous types to return " +
                     "a shaped subset of the data about Customers.")]
        public void DLinq16() {
            var q =
                from c in db.Customers
                select new {
                    c.CustomerID,
                    CompanyInfo = new {c.CompanyName, c.City, c.Country},
                    ContactInfo = new {c.ContactName, c.ContactTitle}
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Select - Nested ")]
        [Description("This sample uses nested queries to return a sequence of " +
                     "all orders containing their OrderID, a subsequence of the " +
                     "items in the order where there is a discount, and the money " +
                     "saved if shipping is not included.")]
        public void DLinq17() {
            var q =
                from o in db.Orders
                select new {
                    o.OrderID,
                    DiscountedProducts =
                        from od in o.OrderDetails
                        where od.Discount > 0.0
                        select od,
                    FreeShippingDiscount = o.Freight
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("SELECT/DISTINCT")]
        [Title("Distinct")]
        [Description("This sample uses Distinct to select a sequence of the unique cities " +
                     "that have Customers.")]
        public void DLinq18() {
            var q = (
                from c in db.Customers
                select c.City )
                .Distinct();

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Count - Simple")]
        [Description("This sample uses Count to find the number of Customers in the database.")]
        public void DLinq19()
        {
            var q = db.Customers.Count();
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Count - Conditional")]
        [Description("This sample uses Count to find the number of Products in the database " +
                     "that are not discontinued.")]
        public void DLinq20() {
            var q = db.Products.Count(p => !p.Discontinued);
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Sum - Simple")]
        [Description("This sample uses Sum to find the total freight over all Orders.")]
        public void DLinq21() {
            var q = db.Orders.Select(o => o.Freight).Sum();
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Sum - Mapped")]
        [Description("This sample uses Sum to find the total number of units on order over all Products.")]
        public void DLinq22() {
            var q = db.Products.Sum(p => p.UnitsOnOrder);
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Min - Simple")]
        [Description("This sample uses Min to find the lowest unit price of any Product.")]
        public void DLinq23() {
            var q = db.Products.Select(p => p.UnitPrice).Min();
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Min - Mapped")]
        [Description("This sample uses Min to find the lowest freight of any Order.")]
        public void DLinq24() {
            var q = db.Orders.Min(o => o.Freight);
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Min - Elements")]
        [Description("This sample uses Min to find the Products that have the lowest unit price " +
                     "in each category.")]
        public void DLinq25() {
            var categories =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    CategoryID = g.Key,
                    CheapestProducts =
                        from p2 in g
                        where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
                        select p2
                };

            ObjectDumper.Write(categories, 1);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Max - Simple")]
        [Description("This sample uses Max to find the latest hire date of any Employee.")]
        public void DLinq26() {
            var q = db.Employees.Select(e => e.HireDate).Max();
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Max - Mapped")]
        [Description("This sample uses Max to find the most units in stock of any Product.")]
        public void DLinq27() {
            var q = db.Products.Max(p => p.UnitsInStock);
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Max - Elements")]
        [Description("This sample uses Max to find the Products that have the highest unit price " +
                     "in each category.")]
        public void DLinq28() {
            var categories =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key,
                    MostExpensiveProducts =
                        from p2 in g
                        where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
                        select p2
                };

            ObjectDumper.Write(categories, 1);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Average - Simple")]
        [Description("This sample uses Average to find the average freight of all Orders.")]
        public void DLinq29() {
            var q = db.Orders.Select(o => o.Freight).Average();
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Average - Mapped")]
        [Description("This sample uses Average to find the average unit price of all Products.")]
        public void DLinq30() {
            var q = db.Products.Average(p => p.UnitPrice);
            Console.WriteLine(q);
        }

        [CategoryAttribute("COUNT/SUM/MIN/MAX/AVG")]
        [Title("Average - Elements")]
        [Description("This sample uses Average to find the Products that have unit price higher than " +
                     "the average unit price of the category for each category.")]
        public void DLinq31() {
            var categories =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key, 
                    ExpensiveProducts =
                        from p2 in g
                        where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
                        select p2
                };

            ObjectDumper.Write(categories, 1);
        }


//        [CategoryAttribute("JOIN")]
//        [Title("SelectMany - 1 to Many - 1")]
//        [Description("This sample uses foreign key navigation in the " +
//                     "from clause to select all orders for customers in London.")]
//        public void DLinqJoin1() {
//            var q =
//                from c in db.Customers
//                from o in c.Orders
//                where c.City == "London"
//                select o;
//
//            ObjectDumper.Write(q);
//        }

        [CategoryAttribute("JOIN")]
        [Title("SelectMany - 1 to Many - 2")]
        [Description("This sample uses foreign key navigation in the " +
                     "where clause to filter for Products whose Supplier is in the USA " +
                     "that are out of stock.")]
        public void DLinqJoin2() {
            var q =
                from p in db.Products
                where p.Supplier.Country == "USA" && p.UnitsInStock == 0
                select p;

            ObjectDumper.Write(q);
        }

//        [CategoryAttribute("JOIN")]
//        [Title("SelectMany - Many to Many")]
//        [Description("This sample uses foreign key navigation in the " +
//                     "from clause to filter for employees in Seattle, " +
//                     "and also list their territories.")]
//        public void DLinqJoin3() {
//            var q =
//                from e in db.Employees
//                from et in e.EmployeeTerritories
//                where e.City == "Seattle"
//                select new {e.FirstName, e.LastName, et.Territory.TerritoryDescription};
//
//            ObjectDumper.Write(q);
//        }

//        [CategoryAttribute("JOIN")]
//        [Title("SelectMany - Self-Join")]
//        [Description("This sample uses foreign key navigation in the " +
//                     "select clause to filter for pairs of employees where " +
//                     "one employee reports to the other and where " +
//                     "both employees are from the same City.")]
//        public void DLinqJoin4() {
//            var q =
//                from e1 in db.Employees
//                from e2 in e1.Employees
//                where e1.City == e2.City
//                select new {
//                    FirstName1 = e1.FirstName, LastName1 = e1.LastName,
//                    FirstName2 = e2.FirstName, LastName2 = e2.LastName,
//                    e1.City
//                };
//
//            ObjectDumper.Write(q);
//        }

        [CategoryAttribute("JOIN")]
        [Title("GroupJoin - Two way join")]
        [Description("This sample explictly joins two tables and projects results from both tables.")]
        public void DLinqJoin5() {
            var q =
                from c in db.Customers
                join o in db.Orders on c.CustomerID equals o.CustomerID into orders
                select new {c.ContactName, OrderCount = orders.Count()};

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("JOIN")]
        [Title("GroupJoin - Three way join")]
        [Description("This sample explictly joins three tables and projects results from each of them.")]
        public void DLinqJoin6() {
            var q =
                from c in db.Customers
                join o in db.Orders on c.CustomerID equals o.CustomerID into ords
                join e in db.Employees on c.City equals e.City into emps
                select new {c.ContactName, ords=ords.Count(), emps=emps.Count()};

            ObjectDumper.Write(q);
        }

//        [CategoryAttribute("JOIN")]
//        [Title("GroupJoin - LEFT OUTER JOIN")]
//        [Description("This sample shows how to get LEFT OUTER JOIN by using DefaultIfEmpty(). The DefaultIfEmpty() method returns null when there is no Order for the Employee.")]
//        public void DLinqJoin7() {
//            var q =
//                from e in db.Employees
//                join o in db.Orders on e equals o.Employee into ords
//                from o in ords.DefaultIfEmpty()
//                select new {e.FirstName, e.LastName, Order = o};
//
//            ObjectDumper.Write(q);
//        }

//        [CategoryAttribute("JOIN")]
//        [Title("GroupJoin - Projected let assignment")]
//        [Description("This sample projects a 'let' expression resulting from a join.")]
//        public void DLinqJoin8() {
//            var q = 
//                from c in db.Customers
//                join o in db.Orders on c.CustomerID equals o.CustomerID into ords
//                let z = c.City + c.Country
//                from o in ords                  
//                select new {c.ContactName, o.OrderID, z};
//
//            ObjectDumper.Write(q);
//        }

//        [CategoryAttribute("JOIN")]
//        [Title("GroupJoin - Composite Key")]
//        [Description("This sample shows a join with a composite key.")]
//        public void DLinqJoin9() {
//            var q =
//                from o in db.Orders
//                from p in db.Products
//                join d in db.OrderDetails 
//                    on new {o.OrderID, p.ProductID} equals new {d.OrderID, d.ProductID}
//                    into details
//                from d in details
//                select new {o.OrderID, p.ProductID, d.UnitPrice};
//
//            ObjectDumper.Write(q);
//        }

//        [CategoryAttribute("JOIN")]
//        [Title("GroupJoin - Nullable\\Nonnullable Key Relationship")]
//        [Description("This sample shows how to construct a join where one side is nullable and the other isn't.")]
//        public void DLinqJoin10() {
//            var q =
//                from o in db.Orders
//                join e in db.Employees 
//                    on o.EmployeeID equals (int?)e.EmployeeID into emps
//                from e in emps
//                select new {o.OrderID, e.FirstName};
//
//            ObjectDumper.Write(q);
//        }

        [CategoryAttribute("ORDER BY")]
        [Title("OrderBy - Simple")]
        [Description("This sample uses orderby to sort Employees " +
                     "by hire date.")]
        public void DLinq36() {
            var q =
                from e in db.Employees
                orderby e.HireDate
                select e;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("ORDER BY")]
        [Title("OrderBy - With Where")]
        [Description("This sample uses where and orderby to sort Orders " +
                     "shipped to London by freight.")]
        public void DLinq37() {
            var q =
                from o in db.Orders
                where o.ShipCity == "London"
                orderby o.Freight
                select o;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("ORDER BY")]
        [Title("OrderByDescending")]
        [Description("This sample uses orderby to sort Products " +
                     "by unit price from highest to lowest.")]
        public void DLinq38() {
            var q =
                from p in db.Products
                orderby p.UnitPrice descending
                select p;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("ORDER BY")]
        [Title("ThenBy")]
        [Description("This sample uses a compound orderby to sort Customers " +
                     "by city and then contact name.")]
        public void DLinq39() {
            var q =
                from c in db.Customers
                orderby c.City, c.ContactName
                select c;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("ORDER BY")]
        [Title("ThenByDescending")]
        [Description("This sample uses orderby to sort Orders from EmployeeID 1 " +
                     "by ship-to country, and then by freight from highest to lowest.")]
        public void DLinq40() {
            var q =
                from o in db.Orders
                where o.EmployeeID == 1
                orderby o.ShipCountry, o.Freight descending
                select o;

            ObjectDumper.Write(q);
        }


        [CategoryAttribute("ORDER BY")]
        [Title("OrderBy - Group By")]
        [Description("This sample uses Orderby, Max and Group By to find the Products that have " +
                     "the highest unit price in each category, and sorts the group by category id.")]
        public void DLinq41() {
            var categories =
                from p in db.Products
                group p by p.CategoryID into g
                orderby g.Key
                select new {
                    g.Key,
                    MostExpensiveProducts =
                        from p2 in g
                        where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
                        select p2
                };

            ObjectDumper.Write(categories, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Simple")]
        [Description("This sample uses group by to partition Products by " +
                     "CategoryID.")]
        public void DLinq42() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                select g;

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Max")]
        [Description("This sample uses group by and Max " +
                     "to find the maximum unit price for each CategoryID.")]
        public void DLinq43() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key,
                    MaxPrice = g.Max(p => p.UnitPrice)
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Min")]
        [Description("This sample uses group by and Min " +
                     "to find the minimum unit price for each CategoryID.")]
        public void DLinq44() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key,
                    MinPrice = g.Min(p => p.UnitPrice)
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Average")]
        [Description("This sample uses group by and Average " +
                     "to find the average UnitPrice for each CategoryID.")]
        public void DLinq45() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key,
                    AveragePrice = g.Average(p => p.UnitPrice)
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Sum")]
        [Description("This sample uses group by and Sum " +
                     "to find the total UnitPrice for each CategoryID.")]
        public void DLinq46() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key,
                    TotalPrice = g.Sum(p => p.UnitPrice)
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Count")]
        [Description("This sample uses group by and Count " +
                     "to find the number of Products in each CategoryID.")]
        public void DLinq47() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key,
                    NumProducts = g.Count()
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Count - Conditional")]
        [Description("This sample uses group by and Count " +
                     "to find the number of Products in each CategoryID " +
                     "that are discontinued.")]
        public void DLinq48() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                select new {
                    g.Key,
                    NumProducts = g.Count(p => p.Discontinued)
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - followed by Where")]
        [Description("This sample uses a where clause after a group by clause " +
                     "to find all categories that have at least 10 products.")]
        public void DLinq49() {
            var q =
                from p in db.Products
                group p by p.CategoryID into g
                where g.Count() >= 10
                select new {
                    g.Key,
                    ProductCount = g.Count()
                };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Multiple Columns")]
        [Description("This sample uses Group By to group products by CategoryID and SupplierID.")]
        public void DLinq50() {
            var categories =
                from p in db.Products
                group p by new { p.CategoryID, p.SupplierID } into g
                select new {g.Key, g};

            ObjectDumper.Write(categories, 1);
        }

        [CategoryAttribute("GROUP BY/HAVING")]
        [Title("GroupBy - Expression")]
        [Description("This sample uses Group By to return two sequences of products. " +
                     "The first sequence contains products with unit price " +
                     "greater than 10. The second sequence contains products " +
                     "with unit price less than or equal to 10.")]
        public void DLinq51() {
            var categories =
                from p in db.Products
                group p by new { Criterion = p.UnitPrice > 10 } into g
                select g;

            ObjectDumper.Write(categories, 1);
        }

        [CategoryAttribute("EXISTS/IN/ANY/ALL")]
        [Title("Any - Simple")]
        [Description("This sample uses Any to return only Customers that have no Orders.")]
        public void DLinq52() {
            var q =
                from c in db.Customers
                where !c.Orders.Any()
                select c;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("EXISTS/IN/ANY/ALL")]
        [Title("Any - Conditional")]
        [Description("This sample uses Any to return only Categories that have " +
                     "at least one Discontinued product.")]
        public void DLinq53() {
            var q =
                from c in db.Categories
                where c.Products.Any(p => p.Discontinued)
                select c;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("EXISTS/IN/ANY/ALL")]
        [Title("All - Conditional")]
        [Description("This sample uses All to return Customers whom all of their orders " +
                     "have been shipped to their own city or whom have no orders.")]
        public void DLinq54() {
            var q =
                from c in db.Customers
                where c.Orders.All(o => o.ShipCity == c.City)
                select c;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("UNION ALL/UNION/INTERSECT")]
        [Title("Concat - Simple")]
        [Description("This sample uses Concat to return a sequence of all Customer and Employee " +
                     "phone/fax numbers.")]
        public void DLinq55() {
            var q = (
                     from c in db.Customers
                     select c.Phone
                    ).Concat(
                     from c in db.Customers
                     select c.Fax
                    ).Concat(
                     from e in db.Employees
                     select e.HomePhone
                    );

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("UNION ALL/UNION/INTERSECT")]
        [Title("Concat - Compound")]
        [Description("This sample uses Concat to return a sequence of all Customer and Employee " +
                     "name and phone number mappings.")]
        public void DLinq56() {
            var q = (
                     from c in db.Customers
                     select new {Name = c.CompanyName, c.Phone}
                    ).Concat(
                     from e in db.Employees
                     select new {Name = e.FirstName + " " + e.LastName, Phone = e.HomePhone}
                    );

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("UNION ALL/UNION/INTERSECT")]
        [Title("Union")]
        [Description("This sample uses Union to return a sequence of all countries that either " +
                     "Customers or Employees are in.")]
        public void DLinq57() {
            var q = (
                     from c in db.Customers
                     select c.Country
                    ).Union(
                     from e in db.Employees
                     select e.Country
                    );

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("UNION ALL/UNION/INTERSECT")]
        [Title("Intersect")]
        [Description("This sample uses Intersect to return a sequence of all countries that both " +
                     "Customers and Employees live in.")]
        public void DLinq58() {
            var q = (
                     from c in db.Customers
                     select c.Country
                    ).Intersect(
                     from e in db.Employees
                     select e.Country
                    );

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("UNION ALL/UNION/INTERSECT")]
        [Title("Except")]
        [Description("This sample uses Except to return a sequence of all countries that " +
                     "Customers live in but no Employees live in.")]
        public void DLinq59() {
            var q = (
                     from c in db.Customers
                     select c.Country
                    ).Except(
                     from e in db.Employees
                     select e.Country
                    );

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("TOP/BOTTOM")]
        [Title("Take")]
        [Description("This sample uses Take to select the first 5 Employees hired.")]
        public void DLinq60() {
            var q = (
                from e in db.Employees
                orderby e.HireDate 
                select e)
                .Take(5);

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("TOP/BOTTOM")]
        [Title("Skip")]
        [Description("This sample uses Skip to select all but the 10 most expensive Products.")]
        public void DLinq61() {
            var q = (
                from p in db.Products
                orderby p.UnitPrice descending
                select p)
                .Skip(10);

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("Paging")]
        [Title("Paging - Index")]
        [Description("This sample uses the Skip and Take operators to do paging by " +
                     "skipping the first 50 records and then returning the next 10, thereby " +
                     "providing the data for page 6 of the Products table.")]
        public void DLinq62() {
            var q = (
                from c in db.Customers
                orderby c.ContactName
                select c)
                .Skip(50)
                .Take(10);

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("Paging")]
        [Title("Paging - Ordered Unique Key")]
        [Description("This sample uses a where clause and the Take operator to do paging by, " +
                     "first filtering to get only the ProductIDs above 50 (the last ProductID " +
                     "from page 5), then ordering by ProductID, and finally taking the first 10 results, " +
                     "thereby providing the data for page 6 of the Products table.  " +
                     "Note that this method only works when ordering by a unique key.")]
        public void DLinq63() {
            var q = (
                from p in db.Products
                where p.ProductID > 50
                orderby p.ProductID
                select p)
                .Take(10);

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("INSERT/UPDATE/DELETE")]
        [Title("Insert - Simple")]
        [Description("This sample uses the Add method to add a new Customer to the " +
                     "Customers Table object.  The call to SubmitChanges persists this " +
                     "new Customer to the database.")]
        public void DLinq64() {
            var q =
                from c in db.Customers
                where c.Region == "WA"
                select c;

            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(q);


            Console.WriteLine();
            Console.WriteLine("*** INSERT ***");
            var newCustomer = new Customer { CustomerID = "MCSFT",
                                             CompanyName = "Microsoft",
                                             ContactName = "John Doe",
                                             ContactTitle = "Sales Manager",
                                             Address = "1 Microsoft Way",
                                             City = "Redmond",
                                             Region = "WA",
                                             PostalCode = "98052",
                                             Country = "USA",
                                             Phone = "(425) 555-1234",
                                             Fax = null
                                           };
            db.Customers.Add(newCustomer);
            db.SubmitChanges();


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
            ObjectDumper.Write(q);



            Cleanup64();  // Restore previous database state
        }

        private void Cleanup64() {
 

            db.Customers.RemoveAll(from c in db.Customers where c.CustomerID == "MCSFT" select c);
            db.SubmitChanges();
        }

        [CategoryAttribute("INSERT/UPDATE/DELETE")]
        [Title("Insert - 1-to-Many")]
        [Description("This sample uses the Add method to add a new Category to the " +
                     "Categories table object, and a new Product to the Products Table " +
                     "object with a foreign key relationship to the new Category.  The call " +
                     "to SubmitChanges persists these new objects and their relationships " +
                     "to the database.")]
        public void DLinq65() {
            var q = (
                from c in db.Categories
                where c.CategoryName == "Widgets"
                select c )
                .Including(c => c.Products);

            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(q, 1);


            Console.WriteLine();
            Console.WriteLine("*** INSERT ***");
            //var newCategory = new Category { CategoryName = "Widgets",
            //                                 Description = "Widgets are the customer-facing analogues " +
            //                                               "to sprockets and cogs."
            //                               };
            //var newProduct = new Product { ProductName = "Blue Widget",
            //                               UnitPrice = 34.56M,
            //                               Category = newCategory
            //                             };
            //db.Categories.Add(newCategory);
            //db.SubmitChanges();


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
            ObjectDumper.Write(q, 1);



            Cleanup65();  // Restore previous database state
        }

        private void Cleanup65() {
 

            db.Products.RemoveAll(from p in db.Products where p.Category.CategoryName == "Widgets" select p);
            db.Categories.RemoveAll(from c in db.Categories where c.CategoryName == "Widgets" select c);
            db.SubmitChanges();
        }

        [CategoryAttribute("INSERT/UPDATE/DELETE")]
        [Title("Insert - Many-to-Many")]
        [Description("This sample uses the Add method to add a new Employee to the " +
                     "Employees table object, a new Territory to the Territories table " +
                     "object, and a new EmployeeTerritory to the EmployeeTerritories table " +
                     "object with foreign key relationships to the new Employee and Territory.  " +
                     "The call to SubmitChanges persists these new objects and their " +
                     "relationships to the database.")]
        public void DLinq66() {
            var q = (
                from e in db.Employees
                where e.FirstName == "Nancy"
                select e )
                .Including(e => e.EmployeeTerritories.Including(et => et.Territory));

            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(q, 1);


            Console.WriteLine();
            Console.WriteLine("*** INSERT ***");
            var newEmployee = new Employee { FirstName = "Kira",
                                             LastName = "Smith"
                                           };
            var newTerritory = new Territory { TerritoryID = "12345",
                                               TerritoryDescription = "Anytown",
                                               Region = db.Regions.First()
                                             };
            var newEmployeeTerritory = new EmployeeTerritory { Employee = newEmployee,
                                                               Territory = newTerritory
                                                             };
            db.Employees.Add(newEmployee);
            db.Territories.Add(newTerritory);
            db.EmployeeTerritories.Add(newEmployeeTerritory);
            db.SubmitChanges();


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
            ObjectDumper.Write(q, 2);



            Cleanup66();  // Restore previous database state
        }

        private void Cleanup66() {
 

            db.EmployeeTerritories.RemoveAll(from et in db.EmployeeTerritories where et.TerritoryID == "12345" select et);
            db.Employees.RemoveAll(from e in db.Employees where e.FirstName == "Kira" && e.LastName == "Smith" select e);
            db.Territories.RemoveAll(from t in db.Territories where t.TerritoryID == "12345" select t);
            db.SubmitChanges();
        }

        [CategoryAttribute("INSERT/UPDATE/DELETE")]
        [Title("Update - Simple")]
        [Description("This sample uses SubmitChanges to persist an update made to a retrieved " +
                     "Customer object back to the database.")]
        public void DLinq67() {
            var q =
                from c in db.Customers
                where c.CustomerID == "ALFKI"
                select c;

            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(q);


            Console.WriteLine();
            Console.WriteLine("*** UPDATE ***");
            Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI");
            cust.ContactTitle = "Vice President";
            db.SubmitChanges();


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
            ObjectDumper.Write(q);



            Cleanup67();  // Restore previous database state
        }

        private void Cleanup67() {
 

            Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI");
            cust.ContactTitle = "Sales Representative";
            db.SubmitChanges();
        }

        [CategoryAttribute("INSERT/UPDATE/DELETE")]
        [Title("Update - Multiple")]
        [Description("This sample uses SubmitChanges to persist updates made to multiple retrieved " +
                     "Product objects back to the database.")]
        public void DLinq68() {
            var q =
                from p in db.Products
                where p.CategoryID == 1
                select p;

            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(q);


            Console.WriteLine();
            Console.WriteLine("*** UPDATE ***");
            foreach (var p in q) {
                p.UnitPrice += 1.00M;
            }
            db.SubmitChanges();


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
            ObjectDumper.Write(q);



            Cleanup68();  // Restore previous database state
        }

        private void Cleanup68() {
 

            var q =
                from p in db.Products
                where p.CategoryID == 1
                select p;

            foreach (var p in q) {
                p.UnitPrice -= 1.00M;
            }
            db.SubmitChanges();
        }

        [CategoryAttribute("INSERT/UPDATE/DELETE")]
        [Title("Delete - Simple")]
        [Description("This sample uses the Remove method to delete a Customer from the " +
                     "Customers Table object.  The call to SubmitChanges persists this " +
                     "deletion to the database.")]
        public void DLinq69() {
            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(from c in db.Customers where c.CustomerID == "WOLZA" select c);


            Console.WriteLine();
            Console.WriteLine("*** DELETE ***");
            Customer cust = db.Customers.First(c => c.CustomerID == "WOLZA");
            db.Customers.Remove(cust);
            db.SubmitChanges();


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
 
            ObjectDumper.Write(from c in db.Customers where c.CustomerID == "WOLZA" select c);



            Cleanup69();  // Restore previous database state
        }

        private void Cleanup69() {
 

            Customer cust = new Customer() {
                CustomerID = "WOLZA",
                CompanyName = "Wolski Zajazd",
                ContactName = "Zbyszek Piestrzeniewicz",
                ContactTitle = "Owner",
                Address = "ul. Filtrowa 68",
                City = "Warszawa",
                Region = null,
                PostalCode = "01-012",
                Country = "Poland",
                Phone = "(26) 642-7012",
                Fax = "(26) 642-7012",
            };
            db.Customers.Add(cust);

            db.SubmitChanges();
        }

        [CategoryAttribute("INSERT/UPDATE/DELETE")]
        [Title("Delete - Many-to-Many")]
        [Description("This sample uses the Remove method to delete a Territory from the " +
                     "Territories Table object, first deleting any related EmployeeTerritory " +
                     "objects.  The call to SubmitChanges persists this deletion to the database.")]
        public void DLinq70() {
            var q =
                from e in db.Employees
                where e.EmployeeTerritories.Any(et => et.TerritoryID == "03049")
                select e;

            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(q);


            Console.WriteLine();
            Console.WriteLine("*** DELETE ***");
            var empTerr =
                from et in db.EmployeeTerritories
                where et.TerritoryID == "03049"
                select et;
            foreach (EmployeeTerritory et in empTerr)
                db.EmployeeTerritories.Remove(et);
            Territory terr = db.Territories.First(t => t.TerritoryID == "03049");
            db.Territories.Remove(terr);
            db.SubmitChanges();


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
            ObjectDumper.Write(q);



            Cleanup70();  // Restore previous database state
        }

        private void Cleanup70() {
 

            Territory terr = new Territory() {
                TerritoryID = "03049",
                TerritoryDescription = "Hollis",
                Region = db.Regions.Where(r=>r.RegionID==3).First()
            };
            EmployeeTerritory empTerr = new EmployeeTerritory() {
                Territory = terr,
                Employee = db.Employees.Where(e=>e.EmployeeID==9).First()
            };
            db.Territories.Add(terr);
            db.EmployeeTerritories.Add(empTerr);
            db.SubmitChanges();
        }

        [CategoryAttribute("Simultaneous Changes")]
        [Title("Optimistic Concurrency - 1")]
        [Description("This and the following sample demonstrates optimistic concurrency.  In this sample, " +
                     "the other user makes and commits his update to Product 1 before you read the data " +
                     "so no conflict occurs.")]
        public void DLinq71() {
            Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");

            // Open a second connection to the database to simulate another user
            // who is going to make changes to the Products table                
            Northwind otherUser_db = new Northwind(connString) { Log = db.Log };

            var otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1);
            otherUser_product.UnitPrice = 999.99M;
            otherUser_db.SubmitChanges();

            Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");


            Console.WriteLine();
            Console.WriteLine("YOU:  ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");

            var product = db.Products.First(p => p.ProductID == 1);
            product.UnitPrice = 777.77M;

            bool conflict = false;
            try {
                db.SubmitChanges();
            }
            catch (OptimisticConcurrencyException) {
                conflict = true;
            }

            Console.WriteLine();
            if (conflict) {
                Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *");
                Console.WriteLine("Another user has changed Product 1 since it was first requested.");
                Console.WriteLine("Backing out changes.");
            }
            else {
                Console.WriteLine("* * * COMMIT SUCCESSFUL * * *");
                Console.WriteLine("Changes to Product 1 saved.");
            }

            Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");



            Cleanup71();  // Restore previous database state
        }

        private void Cleanup71() {
 
 

            var product = db.Products.First(p => p.ProductID == 1);
            product.UnitPrice = 18.00M;
            db.SubmitChanges();
        }

        [CategoryAttribute("Simultaneous Changes")]
        [Title("Optimistic Concurrency - 2")]
        [Description("This and the previous sample demonstrates optimistic concurrency.  In this sample, " +
                     "the other user makes and commits his update to Product 1 after you read the data, " +
                     "but before completing your update, causing an optimistic concurrency conflict.  " +
                     "Your changes are rolled back, allowing you to retrieve the newly updated data " +
                     "from the database and decide how to proceed with your own update.")]
        public void DLinq72() {
            Console.WriteLine("YOU:  ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");

            var product = db.Products.First(p => p.ProductID == 1);

            Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");


            Console.WriteLine();
            Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");

            // Open a second connection to the database to simulate another user
            // who is going to make changes to the Products table                
            Northwind otherUser_db = new Northwind(connString) { Log = db.Log };

            var otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1);
            otherUser_product.UnitPrice = 999.99M;
            otherUser_db.SubmitChanges();

            Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");


            Console.WriteLine();
            Console.WriteLine("YOU (continued): ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");

            product.UnitPrice = 777.77M;

            bool conflict = false;
            try {
                db.SubmitChanges();
            }
            catch (OptimisticConcurrencyException) {
                conflict = true;
            }

            Console.WriteLine();
            if (conflict) {
                Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *");
                Console.WriteLine("Another user has changed Product 1 since it was first requested.");
                Console.WriteLine("Backing out changes.");
            }
            else {
                Console.WriteLine("* * * COMMIT SUCCESSFUL * * *");
                Console.WriteLine("Changes to Product 1 saved.");
            }

            Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ");



            Cleanup72();  // Restore previous database state
        }

        private void Cleanup72()
        {
 
 

            // transaction failure will roll data back automatically
        }

        [CategoryAttribute("Simultaneous Changes")]
        [Title("Transactions - Implicit")]
        [Description("This sample demonstrates the implicit transaction created by " +
                     "SubmitChanges.  The update to prod2's UnitsInStock field " +
                     "makes its value negative, which violates a check constraint " +
                     "on the server.  This causes the transaction that is updating " +
                     "both Products to fail, which rolls back all changes.")]
        public void DLinq73() {
            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p);
            ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p);


            Console.WriteLine();
            Console.WriteLine("*** UPDATE WITH IMPLICIT TRANSACTION ***");
            try {
                Product prod1 = db.Products.First(p => p.ProductID == 4);
                Product prod2 = db.Products.First(p => p.ProductID == 5);
                prod1.UnitsInStock -= 3;
                prod2.UnitsInStock -= 5;    // ERROR: this will make the units in stock negative
                // db.SubmitChanges implicitly uses a transaction so that
                // either both updates are accepted or both are rejected
                db.SubmitChanges();
            }
            catch (Exception e) {
                Console.WriteLine(e.Message);
            }


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
 
            ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p);
            ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p);



            Cleanup73();  // Restore previous database state
        }

        private void Cleanup73()
        {
 

            // transaction failure will roll data back automatically
        }

        [CategoryAttribute("Simultaneous Changes")]
        [Title("Transactions - Explicit")]
        [Description("This sample demonstrates using an explicit transaction.  This " +
                     "provides more protection by including the reading of the data in the " +
                     "transaction to help prevent optimistic concurrency exceptions.  " +
                     "As in the previous query, the update to prod2's UnitsInStock field " +
                     "makes the value negative, which violates a check constraint within " +
                     "the database.  This causes the transaction that is updating both " +
                     "Products to fail, which rolls back all changes.")]
        public void DLinq74() {
            Console.WriteLine("*** BEFORE ***");
            ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p);
            ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p);


            Console.WriteLine();
            Console.WriteLine("*** UPDATE WITH EXPLICIT TRANSACTION ***");
            // Explicit use of TransactionScope ensures that
            // the data will not change in the database between
            // read and write
            using (TransactionScope ts = new TransactionScope()) {
                try {
                    Product prod1 = db.Products.First(p => p.ProductID == 4);
                    Product prod2 = db.Products.First(p => p.ProductID == 5);
                    prod1.UnitsInStock -= 3;
                    prod2.UnitsInStock -= 5;    // ERROR: this will make the units in stock negative
                    db.SubmitChanges();
                }
                catch (Exception e) {
                    Console.WriteLine(e.Message);
                }
            }


            Console.WriteLine();
            Console.WriteLine("*** AFTER ***");
 
            ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p);
            ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p);



            Cleanup74();  // Restore previous database state
        }

        private void Cleanup74()
        {
 

            // transaction failure will roll data back automatically
        }

        [CategoryAttribute("NULL")]
        [Title("null")]
        [Description("This sample uses the null value to find Employees " +
                     "that do not report to another Employee.")]
        public void DLinq75() {
            var q =
                from e in db.Employees
                where e.ReportsToEmployee == null
                select e;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("NULL")]
        [Title("Nullable<T>.HasValue")]
        [Description("This sample uses Nullable<T>.HasValue to find Employees " +
                     "that do not report to another Employee.")]
        public void DLinq76() {
            var q =
                from e in db.Employees
                where !e.ReportsTo.HasValue
                select e;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("NULL")]
        [Title("Nullable<T>.Value")]
        [Description("This sample uses Nullable<T>.Value for Employees " +
                     "that report to another Employee to return the " +
                     "EmployeeID number of that employee.  Note that " +
                     "the .Value is optional.")]
        public void DLinq77() {
            var q =
                from e in db.Employees
                where e.ReportsTo.HasValue
                select new {e.FirstName, e.LastName, ReportsTo = e.ReportsTo.Value};

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String Concatenation")]
        [Description("This sample uses the + operator to concatenate string fields " +
                     "and string literals in forming the Customers' calculated " +
                     "Location value.")]
        public void DLinq78() {
            var q =
                from c in db.Customers
                select new { c.CustomerID, Location = c.City + ", " + c.Country };

            ObjectDumper.Write(q, 1);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Length")]
        [Description("This sample uses the Length property to find all Products whose " +
                     "name is shorter than 10 characters.")]
        public void DLinq79() {
            var q =
                from p in db.Products
                where p.ProductName.Length < 10
                select p;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Contains(substring)")]
        [Description("This sample uses the Contains method to find all Customers whose " +
                     "contact name contains 'Anders'.")]
        public void DLinq80() {
            var q =
                from c in db.Customers
                where c.ContactName.Contains("Anders")
                select c;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.IndexOf(substring)")]
        [Description("This sample uses the IndexOf method to find the first instance of " +
                     "a space in each Customer's contact name.")]
        public void DLinq81() {
            var q =
                from c in db.Customers
                select new {c.ContactName, SpacePos = c.ContactName.IndexOf(" ")};

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.StartsWith(prefix)")]
        [Description("This sample uses the StartsWith method to find Customers whose " +
                     "contact name starts with 'Maria'.")]
        public void DLinq82() {
            var q =
                from c in db.Customers
                where c.ContactName.StartsWith("Maria")
                select c;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.EndsWith(suffix)")]
        [Description("This sample uses the StartsWith method to find Customers whose " +
                     "contact name ends with 'Anders'.")]
        public void DLinq83() {
            var q =
                from c in db.Customers
                where c.ContactName.EndsWith("Anders")
                select c;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Substring(start)")]
        [Description("This sample uses the Substring method to return Product names starting " +
                     "from the fourth letter.")]
        public void DLinq84() {
            var q =
                from p in db.Products
                select p.ProductName.Substring(3);

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Substring(start, length)")]
        [Description("This sample uses the Substring method to find Employees whose " +
                     "home phone numbers have '555' as the seventh through ninth digits.")]
        public void DLinq85() {
            var q =
                from e in db.Employees
                where e.HomePhone.Substring(6, 3) == "555"
                select e;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.ToUpper()")]
        [Description("This sample uses the ToUpper method to return Employee names " +
                     "where the last name has been converted to uppercase.")]
        public void DLinq86() {
            var q =
                from e in db.Employees
                select new {LastName = e.LastName.ToUpper(), e.FirstName};

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.ToLower()")]
        [Description("This sample uses the ToLower method to return Category names " +
                     "that have been converted to lowercase.")]
        public void DLinq87() {
            var q =
                from c in db.Categories
                select c.CategoryName.ToLower();

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Trim()")]
        [Description("This sample uses the Trim method to return the first five " +
                     "digits of Employee home phone numbers, with leading and " +
                     "trailing spaces removed.")]
        public void DLinq88() {
            var q =
                from e in db.Employees
                select e.HomePhone.Substring(0, 5).Trim();

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Insert(pos, str)")]
        [Description("This sample uses the Insert method to return a sequence of " +
                     "employee phone numbers that have a ) in the fifth position, " +
                     "inserting a : after the ).")]
        public void DLinq89() {
            var q =
                from e in db.Employees
                where e.HomePhone.Substring(4, 1) == ")"
                select e.HomePhone.Insert(5, ":");

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Remove(start)")]
        [Description("This sample uses the Insert method to return a sequence of " +
                     "employee phone numbers that have a ) in the fifth position, " +
                     "removing all characters starting from the tenth character.")]
        public void DLinq90() {
            var q =
                from e in db.Employees
                where e.HomePhone.Substring(4, 1) == ")"
                select e.HomePhone.Remove(9);

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Remove(start, length)")]
        [Description("This sample uses the Insert method to return a sequence of " +
                     "employee phone numbers that have a ) in the fifth position, " +
                     "removing the first six characters.")]
        public void DLinq91() {
            var q =
                from e in db.Employees
                where e.HomePhone.Substring(4, 1) == ")"
                select e.HomePhone.Remove(0, 6);

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("String.Replace(find, replace)")]
        [Description("This sample uses the Replace method to return a sequence of " +
                     "Supplier information where the Country field has had " +
                     "UK replaced with United Kingdom and USA replaced with " +
                     "United States of America.")]
        public void DLinq92() {
            var q =
                from s in db.Suppliers
                select new {
                    s.CompanyName,
                    Country = s.Country.Replace("UK", "United Kingdom")
                                       .Replace("USA", "United States of America")
                };

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("DateTime.Year")]
        [Description("This sample uses the DateTime's Year property to " +
                     "find Orders placed in 1997.")]
        public void DLinq93() {
            var q =
                from o in db.Orders
                where o.OrderDate.Value.Year == 1997
                select o;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("DateTime.Month")]
        [Description("This sample uses the DateTime's Month property to " +
                     "find Orders placed in December.")]
        public void DLinq94() {
            var q =
                from o in db.Orders
                where o.OrderDate.Value.Month == 12
                select o;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("String/Date Functions")]
        [Title("DateTime.Day")]
        [Description("This sample uses the DateTime's Day property to " +
                     "find Orders placed on the 31st day of the month.")]
        public void DLinq95() {
            var q =
                from o in db.Orders
                where o.OrderDate.Value.Day == 31
                select o;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("Object Identity")]
        [Title("Object Caching - 1")]
        [Description("This sample demonstrates how, upon executing the same query twice, " +
                     "you will receive a reference to the same object in memory each time.")]
        public void DLinq96() {
            Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");
            Customer cust2 = db.Customers.First(c => c.CustomerID == "BONAP");

            Console.WriteLine("cust1 and cust2 refer to the same object in memory: {0}",
                              Object.ReferenceEquals(cust1, cust2));
        }

        [CategoryAttribute("Object Identity")]
        [Title("Object Caching - 2")]
        [Description("This sample demonstrates how, upon executing different queries that " +
                     "return the same row from the database, you will receive a " +
                     "reference to the same object in memory each time.")]
        public void DLinq97() {
            Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");
            Customer cust2 = (
                from o in db.Orders
                where o.Customer.CustomerID == "BONAP"
                select o )
                .First()
                .Customer;

            Console.WriteLine("cust1 and cust2 refer to the same object in memory: {0}",
                              Object.ReferenceEquals(cust1, cust2));
        }

        [CategoryAttribute("Object Loading")]
        [Title("Deferred Loading - 1")]
        [Description("This sample demonstrates how navigating through relationships in " +
                     "retrieved objects can end up triggering new queries to the database " +
                     "if the data was not requested by the original query.")]
        public void DLinq98() {
            var custs =
                from c in db.Customers
                where c.City == "Sao Paulo"
                select c;

            foreach (var cust in custs) {
                foreach (var ord in cust.Orders) {
                    Console.WriteLine("CustomerID {0} has an OrderID {1}.", cust.CustomerID, ord.OrderID);
                }
            }
        }

        [CategoryAttribute("Object Loading")]
        [Title("Including - Eager Loading - 1")]
        [Description("This sample demonstrates how to use Including to request related " +
                     "data during the original query so that additional roundtrips to the " +
                     "database are not required later when navigating through " +
                     "the retrieved objects.")]
        public void DLinq99() {
            var custs = (
                from c in db.Customers
                where c.City == "Sao Paulo"
                select c )
                .Including(c => c.Orders);

            foreach (var cust in custs) {
                foreach (var ord in cust.Orders) {
                    Console.WriteLine("CustomerID {0} has an OrderID {1}.", cust.CustomerID, ord.OrderID);
                }
            }
        }

        [CategoryAttribute("Object Loading")]
        [Title("Deferred Loading - 2")]
        [Description("This sample demonstrates how navigating through relationships in " +
                     "retrieved objects can end up triggering new queries to the database " +
                     "if the data was not requested by the original query.")]
        public void DLinq100() {
            var custs =
                from c in db.Customers
                where c.City == "London"
                select c;

            foreach (var cust in custs) {
                foreach (var ord in cust.Orders) {
                    foreach (var orderDetail in ord.OrderDetails) {
                        Console.WriteLine("CustomerID {0} has an OrderID {1} with ProductID {2} that has name {3}.",
                            cust.CustomerID, ord.OrderID, orderDetail.ProductID, orderDetail.Product.ProductName);
                    }
                }
            }
        }

        [CategoryAttribute("Object Loading")]
        [Title("Including - Eager Loading - 2")]
        [Description("This sample demonstrates how to use Including to request related " +
                     "data during the original query so that additional roundtrips to the " +
                     "database are not required later when navigating through " +
                     "the retrieved objects.")]
        public void DLinq101() {
            var custs = (
                from c in db.Customers
                where c.City == "London"
                select c )
                .Including(c => c.Orders.Including(o => o.OrderDetails.Including(od => od.Product)));

            foreach (var cust in custs) {
                foreach (var ord in cust.Orders) {
                    foreach (var orderDetail in ord.OrderDetails) {
                        Console.WriteLine("CustomerID {0} has an OrderID {1} with ProductID {2} that has name {3}.",
                            cust.CustomerID, ord.OrderID, orderDetail.ProductID, orderDetail.Product.ProductName);
                    }
                }
            }
        }

        private bool isValidProduct(nwind.Product p)
        {
            return p.ProductName.LastIndexOf('C') == 0;
        }

        [CategoryAttribute("Object Loading")]
        [Title("Deferred Loading - (1:M)")]
        [Description("This sample demonstrates how navigating through relationships in " +
             "retrieved objects can result in triggering new queries to the database " +
             "if the data was not requested by the original query.")]
        public void DLinq102() {
            var emps = from e in db.Employees
                       select e;

            foreach (var emp in emps) {
                foreach (var man in emp.Employees) {
                    Console.WriteLine("Employee {0} reported to Manager {1}.", emp.FirstName, man.FirstName);
                }
            }
        }

        [CategoryAttribute("Object Loading")]
        [Title("Including - Eager Loading - (1:M)")]
        [Description("This sample demonstrates how to use Including to request related " +
                     "data during the original query so that additional roundtrips to the " +
                     "database are not triggered while navigating through " +
                     "the retrieved objects.")]
        public void DLinq103() {
            var emps = (
                from e in db.Employees
                select e )
                .Including(e => e.Employees);

            foreach (var emp in emps) {
                foreach (var man in emp.Employees) {
                    Console.WriteLine("Employee {0} reported to Manager {1}.", emp.FirstName, man.FirstName);
                }
            }
        }


        [CategoryAttribute("Object Loading")]
        [Title("Deferred Loading - (BLOB)")]
        [Description("This sample demonstrates how navigating through Link in " +
             "retrieved objects can end up triggering new queries to the database " +
             "if the data type is Link.")]
        public void DLinq104() {
            var emps = from c in db.Employees
                       select c;

           /* foreach (Employee emp in emps) {
                Console.WriteLine("{0}", emp.Notes); 
            
            } */
        }
        [CategoryAttribute("Conversion Operators")]
        [Title("ToSequence")]
        [Description("This sample uses ToSequence so that the client-side IEnumerable<T> " +
                     "implementation of Where is used, instead of the default IQueryable<T> " +
                     "implementation which would be converted to SQL and executed " +
                     "on the server.  This is necessary because the where clause " +
                     "references a user-defined client-side method, isValidProduct, " +
                     "which cannot be converted to SQL.")]
        [LinkedMethod("isValidProduct")]
        public void DLinq105() {
            var q =
                from p in db.Products.ToSequence()
                where isValidProduct(p)
                select p;

            ObjectDumper.Write(q);
        }

        [CategoryAttribute("Conversion Operators")]
        [Title("ToArray")]
        [Description("This sample uses ToArray to immediately evaluate a query into an array " +
                     "and get the 3rd element.")]
        public void DLinq106() {
            var q =
                from c in db.Customers
                where c.City == "London"
                select c;

            Customer[] qArray = q.ToArray();
            ObjectDumper.Write(qArray[3], 0);
        }

        [CategoryAttribute("Conversion Operators")]
        [Title("ToList")]
        [Description("This sample uses ToList to immediately evaluate a query into a List<T>.")]
        public void DLinq107() {
            var q =
                from e in db.Employees
                where e.HireDate >= new DateTime(1994, 1, 1)
                select e;

            List<Employee> qList = q.ToList();
            ObjectDumper.Write(qList, 0);
        }

        [CategoryAttribute("Conversion Operators")]
        [Title("ToDictionary")]
        [Description("This sample uses ToDictionary to immediately evaluate a query and " +
                     "a key expression into an Dictionary<K, T>.")]
        public void DLinq108() {
            var q =
                from p in db.Products
                where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued
                select p;

            Dictionary<int, Product> qDictionary = q.ToDictionary(p => p.ProductID);

            foreach (int key in qDictionary.Keys) {
                Console.WriteLine("Key {0}:", key);
                ObjectDumper.Write(qDictionary[key]);
                Console.WriteLine();
            }
        }

    }
}
